package com.gm.baseplatform.core.jdbc;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import java.sql.SQLException;

public class TblOperator {

    private static Log log = LogFactory.getLog(TblOperator.class);

    public TblOperator() {

    }


    public boolean doSave(String tblname, String[] fieldList, String[] valueList) {
        String asql = "insert into " + tblname + "(";
        
        for (int i = 0; i < fieldList.length; i++) {
            if ((i + 1) == fieldList.length) {
                asql += fieldList[i] + ")";
            } else {
                asql += fieldList[i] + ",";
            }
        }

        asql += " values(";

        for (int i = 0; i < fieldList.length; i++) {
            if ((i + 1) == fieldList.length) {
                asql += "?)";
            } else {
                asql += "?,";
            }
        }

        try {
            return JDBCTool.doSQLUpdate(asql, valueList);
        } catch (SQLException e) {
            log.debug("SQLException,sql{" + asql + "}");
            log.debug("SQLException" + getString(valueList));
            log.debug("SQLException[" + e.toString() + "]");
            return false;
        }

    }


    public boolean doSave(String tblname, String[] fieldList, Object[] valueList) {
        String asql = "insert into " + tblname + "(";

        for (int i = 0; i < fieldList.length; i++) {
            if ((i + 1) == fieldList.length) {
                asql += fieldList[i] + ")";
            } else {
                asql += fieldList[i] + ",";
            }
        }

        asql += " values(";

        for (int i = 0; i < fieldList.length; i++) {
            if ((i + 1) == fieldList.length) {
                asql += "?)";
            } else {
                asql += "?,";
            }
        }

        try {
            return JDBCTool.doSQLUpdate(asql, valueList);
        } catch (SQLException e) {
            log.debug("SQLException,sql{" + asql + "}");
            log.debug("SQLException" + getString(valueList));
            log.debug("SQLException[" + e.toString() + "]");
            return false;
        }

    }


    public boolean doUpdate(String tblname, String[] fieldList, String[] valueList, String keyField, String keyValue) {

        String asql = " update " + tblname + "";
        for (int i = 0; i < fieldList.length; i++) {

            if (i == 0) {
                asql += " set " + fieldList[i] + " = ?, ";
            } else if ((i + 1) == fieldList.length) {
                asql += fieldList[i] + " = ?";
            } else {
                asql += fieldList[i] + " = ?, ";
            }
        }

        asql += " where " + keyField + " = ?";

        String[] tmp = new String[valueList.length + 1];
        for (int i = 0; i < valueList.length; i++) {
            tmp[i] = valueList[i];
        }

        tmp[valueList.length] = keyValue;

        try {
            return JDBCTool.doSQLUpdate(asql, tmp);
        } catch (SQLException e) {
            log.debug("SQLException,sql{" + asql + "}");
            log.debug("SQLException" + getString(tmp));
            log.debug("SQLException[" + e.toString() + "]");
            return false;
        }
    }


    public boolean doUpdate(String tblname, String[] fieldList, Object[] valueList, String keyField, String keyValue) {

        String asql = " update " + tblname + "";
        for (int i = 0; i < fieldList.length; i++) {

            if (i == 0) {
                asql += " set " + fieldList[i] + " = ?, ";
            } else if ((i + 1) == fieldList.length) {
                asql += fieldList[i] + " = ?";
            } else {
                asql += fieldList[i] + " = ?, ";
            }
        }

        asql += " where " + keyField + " = ?";

        Object[] tmp = new Object[valueList.length + 1];
        for (int i = 0; i < valueList.length; i++) {
            tmp[i] = valueList[i];
        }

        tmp[valueList.length] = keyValue;

        try {
            return JDBCTool.doSQLUpdate(asql, tmp);
        } catch (SQLException e) {
        	log.debug("SQLException,sql{" + asql + "}");
            log.debug("SQLException" + getString(tmp));
            log.debug("SQLException[" + e.toString() + "]");
            return false;
        }
    }


    public boolean doUpdate(String tblname, String[] fieldList, Object[] valueList, String keyField, Object keyValue) {

        String asql = " update " + tblname + "";
        for (int i = 0; i < fieldList.length; i++) {

            if (i == 0) {
                asql += " set " + fieldList[i] + " = ?, ";
            } else if ((i + 1) == fieldList.length) {
                asql += fieldList[i] + " = ?";
            } else {
                asql += fieldList[i] + " = ?, ";
            }
        }

        asql += " where " + keyField + " = ?";

        Object[] tmp = new Object[valueList.length + 1];
        for (int i = 0; i < valueList.length; i++) {
            tmp[i] = valueList[i];
        }

        tmp[valueList.length] = keyValue;

        try {
            return JDBCTool.doSQLUpdate(asql, tmp);
        } catch (SQLException e) {
        	log.debug("SQLException,sql{" + asql + "}");
            log.debug("SQLException" + getString(tmp));
            log.debug("SQLException[" + e.toString() + "]");
            return false;
        }
    }


    public boolean doDelete(String tblname, String keyField, String keyValue) {
        String asql = "delete from " + tblname + "";
        asql += " where " + keyField + " = ?";
        String[] tmp = new String[1];
        tmp[0] = keyValue;

        try {
            return JDBCTool.doSQLUpdate(asql, tmp);
        } catch (SQLException e) {
        	log.debug("SQLException,sql{" + asql + "}");
            log.debug("SQLException" + getString(tmp));
            log.debug("SQLException[" + e.toString() + "]");
            return false;
        }
    }


    public boolean doDelete(String tblname, String keyField, Object keyValue) {
        String asql = "delete from " + tblname + "";
        asql += " where " + keyField + " = ?";
        Object[] tmp = new Object[1];
        tmp[0] = keyValue;

        try {
            return JDBCTool.doSQLUpdate(asql, tmp);
        } catch (SQLException e) {
        	log.debug("SQLException,sql{" + asql + "}");
            log.debug("SQLException" + getString(tmp));
            log.debug("SQLException[" + e.toString() + "]");
            return false;
        }
    }


    public String[][] doFindByAnd(String tblname, String[] showlist, String[] fieldList, String[] fieldValue) {
        String asql = "select ";
        for (int i = 0; i < showlist.length; i++) {
            if ((i + 1) == showlist.length) {
                asql += showlist[i];
            } else {
                asql += showlist[i] + ",";
            }
        }

        asql += " from " + tblname + " where 1=1 ";

        for (int i = 0; i < fieldList.length; i++) {
            asql += " and " + fieldList[i] + " = ?";
        }

        try {
            return JDBCTool.doSQLQuery(asql, fieldValue);
        } catch (SQLException e) {
        	log.debug("SQLException,sql{" + asql + "}");
            log.debug("SQLException" + getString(fieldValue));
            log.debug("SQLException[" + e.toString() + "]");
            return null;
        }
    }

    /**
     *
     * @param tblname_P
     * @param showList_P
     * @param fieldList_P
     * @param fieldValue_P
     * @param keyField_P
     * @param tblname_F
     * @param showList_F
     * @param keyField_F
     * @return
     */
    public String[][] doFindByFK(String tblname_P, String[] showList_P, String[] fieldList_P, String[] fieldValue_P, String keyField_P,
                                 String tblname_F, String[] showList_F, String keyField_F) {

        String asql = "select";
        for (int i = 0; i < showList_P.length; i++) {
            if ((i + 1) == showList_P.length) {
                asql += " a." + showList_P[i];
            } else {
                asql += " a." + showList_P[i] + ",";
            }
        }

        if (!asql.equals("select")) {
            asql += " ,";
        }

        for (int i = 0; i < showList_F.length; i++) {
            if ((i + 1) == showList_F.length) {
                asql += " b." + showList_F[i];
            } else {
                asql += " b." + showList_F[i] + ",";
            }
        }


        asql += " from " + tblname_P + "  a,  " + tblname_F + "  b";
        asql += " where 1=1";
        asql += " and a." + keyField_P + " = " + "b." + keyField_F;

        for (int i = 0; i < fieldList_P.length; i++) {
            asql += " and a." + fieldList_P[i] + " = ?";
        }


        try {
            return JDBCTool.doSQLQuery(asql, fieldValue_P);
        } catch (SQLException e) {
        	log.debug("SQLException,sql{" + asql + "}");
            log.debug("SQLException" + getString(fieldList_P));
            log.debug("SQLException[" + e.toString() + "]");
            return null;
        }
    }
    
    /**
     *
     * @param tblname_P
     * @param showList_P
     * @param fieldList_P
     * @param fieldValue_P
     * @param keyField_P
     * @param tblname_F
     * @param showList_F
     * @param fieldList_F
     * @param fieldlValue_F
     * @param keyField_F
     * @return
     */

    public String[][] doFindByFK(String tblname_P, String[] showList_P, String[] fieldList_P, String[] fieldValue_P, String keyField_P,
                                 String tblname_F, String[] showList_F, String[] fieldList_F, String[] fieldlValue_F, String keyField_F) {

        String asql = "select";
        for (int i = 0; i < showList_P.length; i++) {
            if ((i + 1) == showList_P.length) {
                asql += " a." + showList_P[i];
            } else {
                asql += " a." + showList_P[i] + ",";
            }
        }

        if (!asql.equals("select")) {
            asql += " ,";
        }

        for (int i = 0; i < showList_F.length; i++) {
            if ((i + 1) == showList_F.length) {
                asql += " b." + showList_F[i];
            } else {
                asql += " b." + showList_F[i] + ",";
            }
        }


        asql += " from " + tblname_P + "  a,  " + tblname_P + "  b";
        asql += " where 1=1";
        asql += " and a." + keyField_P + " = " + "b." + keyField_F;

        for (int i = 0; i < fieldList_P.length; i++) {
            asql += " and a." + fieldList_P[i] + " = ?";
        }

        for (int i = 0; i < fieldList_F.length; i++) {
            asql += " and b." + fieldList_F[i] + " = ?";
        }

        String[] tmp = new String[fieldList_P.length + fieldList_F.length];

        try {
            return JDBCTool.doSQLQuery(asql, tmp);
        } catch (SQLException e) {
        	log.debug("SQLException,sql{" + asql + "}");
            log.debug("SQLException" + getString(tmp));
            log.debug("SQLException[" + e.toString() + "]");
            return null;
        }
    }


    public String[][] doFindByMul(String tblname_f, String[] showlist_f, String[] fieldlist_f, String[] valuelist_f, String keyfield_f,
                                  String tblname_s, String keyfield_s, String tblname_t, String keyfield_t, String[] showlist_t){

        String asql = "select ";
        for (int i = 0; i < showlist_f.length; i++) {
            if ((i + 1) == showlist_f.length) {
                asql += " a." + showlist_f[i];
            } else {
                asql += " a." + showlist_f[i] + ",";
            }
        }


        if (!asql.equals("select")) {
            asql += " ,";
        }


        for (int i = 0; i < showlist_t.length; i++) {
            if ((i + 1) == showlist_t.length) {
                asql += " c." + showlist_t[i];
            } else {
                asql += " c." + showlist_t[i] + ",";
            }
        }


        asql += " from " + tblname_f + "  a,  " + tblname_s + "  b, " + tblname_t + "c ";
        asql += " where 1=1";

        asql += " and a."+keyfield_f + " = b."+keyfield_s ;
        asql += " and b."+keyfield_s + " = c."+keyfield_t;


        for (int i = 0; i < fieldlist_f.length; i++) {
            asql += " and a." + fieldlist_f[i] + " = ?";
        }



        try {
            return JDBCTool.doSQLQuery(asql, valuelist_f);
        } catch (SQLException e) {
        	log.debug("SQLException,sql{" + asql + "}");
            log.debug("SQLException" + getString(valuelist_f));
            log.debug("SQLException[" + e.toString() + "]");
            return null;
        }

    }


    public String[][] doFindByUnid(String tblname, String keyField ,String keyValue, String[] showList) {
        String asql = " select ";
        for (int i=0; i<showList.length; i++){
            if ((i+1) == showList.length){
                asql += " " + showList[i];
            }else{
                asql += " " + showList[i] + ",";
            }
        }

        asql += " from "+tblname+"";

        asql += " where "+keyField+" = ? ";

        String[] values = new String[1];
        values[0] = new String(keyValue);

        try {
            return JDBCTool.doSQLQuery(asql, values);
        } catch (SQLException e) {
        	log.debug("SQLException,sql{" + asql + "}");
            log.debug("SQLException" + getString(values));
            log.debug("SQLException[" + e.toString() + "]");
            return null;
        }

    }


    private String getString(String[] tmp) {

        String result = "<";
        for (int i = 0; i < tmp.length; i++) {
            if ((i + 1) == tmp.length) {
                result += tmp[i] + ">";
            } else {
                result += tmp[i] + ",";
            }
        }
        return result;
    }


    private String getString(Object[] tmp){
        String result = "<";
        for (int i = 0; i < tmp.length; i++) {
            if ((i + 1) == tmp.length) {
                result += tmp[i] + ">";
            } else {
                result += tmp[i] + ",";
            }
        }
        return result;
    }


    public String[][] doLoadAll(String tblname, String[] showlist){
        String asql = "select ";

        for(int i=0; i<showlist.length; i++){
            if ((i+1) == showlist.length){
                asql += showlist[i] + " ";
            }else{
                asql += showlist[i]  + ",";
            }
        }

        asql += "  from " + tblname;

        try {
            return JDBCTool.doSQLQuery(asql);
        } catch (SQLException e) {
        	log.debug("SQLException,sql{" + asql + "}");
            log.debug("SQLException[" + e.toString() + "]");
            return null;
        }
    }


}
